package com.testingedu.jdbcDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.model.TEmployment;
import com.testingedu.model.TMenu;
import com.testingedu.model.TUser;
import com.testingedu.utils.Format;
import com.testingedu.utils.TimeUtils;

/**
 * 表TStudyState jdbc类 最后修改：2012-11-23 修改人:bfc
 * 
 * @author Administrator
 * 
 */
public class TEmploymentDao extends BaseDao<TMenu> {
	public TEmploymentDao() {
		clazz = TEmployment.class;
	}

	/**
	 * 增加学习状态
	 * 
	 * @param tStudyState
	 * @return
	 */
	public int add(TEmployment tEmployment, int flag) throws Exception{
		String sql1 = "insert into t_employment (com_name,position,_money,description,user_id,start_time) value(?,?,?,?,?,?)";
		String sql2 = "insert into t_employment (com_name,position,_money,description,user_id,start_time,end_time) value(?,?,?,?,?,?,?)";
		String sql = null;
		if(flag == 2) {
			sql = sql2;
		} else {
			sql = sql1;
		}
		List<Object> values = new ArrayList<Object>();
		values.add(tEmployment.getComName());
		values.add(tEmployment.getPosition());
		values.add(tEmployment.getMoney());
		values.add(tEmployment.getDescription());
		values.add(tEmployment.gettUser().getId());
		values.add(tEmployment.getStartTime());
		if(flag == 2) {
			values.add(tEmployment.getEndTime());
		}
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return getMaxId();
	}

	private int getMaxId() throws Exception{
		String sql = "select max(id) as _id from t_employment";
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, null);
			if (rs.next()) {
				return rs.getInt("_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return 0;
	}

	public void delete(int id) throws Exception{
		String sql = "delete from t_employment where id=?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	public void update(TEmployment tEmployment, String em) throws Exception{
		String sql = null;
		List<Object> values = new ArrayList<Object>();
		TEmployment dbTemployment = this.info(tEmployment.getId());
		if(dbTemployment.getEndTime() == null || !dbTemployment.isLeave()) {
			if("no".equals(em)) {
				sql = "update  t_employment set start_time = ?, com_name = ?,position=?,_money=?,description=? where id=?";
				values.add(tEmployment.getStartTime());
			} else {
				if("yes".equals(em)) {
					sql = "update  t_employment set start_time = ?, end_time = ?, com_name = ?,position=?,_money=?,description=? where id=?";
					values.add(tEmployment.getStartTime());
					values.add(tEmployment.getEndTime());
				}
			}
		} else {
			if("yes".equals(em)) {
				sql = "update  t_employment set start_time = ?, end_time = ?, com_name = ?,position=?,_money=?,description=? where id=?";
				values.add(tEmployment.getStartTime());
				values.add(tEmployment.getEndTime());
			} else {
				if("no".equals(em)) {
					sql = "update  t_employment set start_time = ?, end_time = ?, com_name = ?,position=?,_money=?,description=? where id=?";
					values.add(tEmployment.getStartTime());
					values.add(TimeUtils.yesterday(tEmployment.getStartTime()));
				}
			}
		}
		values.add(tEmployment.getComName());
		values.add(tEmployment.getPosition());
		values.add(tEmployment.getMoney());
		values.add(tEmployment.getDescription());
		values.add(tEmployment.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	public void list(QueryResult queryResult, TEmployment tEmployment, Integer userId) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_employment te where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tEmployment != null) {
			if(tEmployment.gettUser() != null && tEmployment.gettUser().getName() != null && tEmployment.gettUser().getName().trim().length() > 0) {
				sql.append( " and te.user_id in (select tu.id from t_user tu where tu.name like ?)");
				values.add("%"+tEmployment.gettUser().getName()+"%");
			}
			if(tEmployment.getComName() != null && tEmployment.getComName().trim().length() > 0) {
				sql.append(" and te.con_name like ?");
				values.add("%"+tEmployment.getComName()+"%");
			}
		}
		if(userId != null && userId > 0) {
			sql.append(" and te.user_id = ?");
			values.add(userId);
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			if (rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TEmployment info(int id) throws Exception{
		String sql = "select * from t_employment te where te.id = ?";
		TEmployment result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToListForInfo(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.getId() > 0) {
			return result;
		}
		return null;
	}

	public List<TEmployment> _resultSetToList(ResultSet rs) throws Exception{
		List<TEmployment> result = new ArrayList<TEmployment>();
		TUserDao tUserDao = new TUserDao();
		try {
			while (rs.next()) {
				TEmployment tEmployment = new TEmployment();
				tEmployment.setId(rs.getInt("id"));
//				Date startTime = rs.getDate("start_time");
//				Date endTime = rs.getDate("end_time");
//				if(startTime != null) {
//					tEmployment.setStartTime(Format.formatDateDay(startTime));
//				}
//				if(endTime != null) {
//					tEmployment.setEndTime(Format.formatDateDay(endTime));
//				}
				tEmployment.setComName(rs.getString("com_name"));
//				tEmployment.setDescription(rs.getString("description"));
				tEmployment.setMoney(rs.getDouble("_money"));
				tEmployment.setPosition(rs.getString("position"));
				int userId = rs.getInt("user_id");
				TUser tUser = tUserDao.info(userId);
				tEmployment.settUser(tUser);
				result.add(tEmployment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
	public TEmployment _resultSetToListForInfo(ResultSet rs) throws Exception{
		TUserDao tUserDao = new TUserDao();
		TEmployment tEmployment = new TEmployment();
		try {
			while (rs.next()) {
				tEmployment.setId(rs.getInt("id"));
				Date startTime = rs.getDate("start_time");
				Date endTime = rs.getDate("end_time");
				if(startTime != null) {
					tEmployment.setStartTime(Format.formatDateDay(startTime));
				}
				if(endTime != null) {
					tEmployment.setEndTime(Format.formatDateDay(endTime));
					if(startTime.getTime() < endTime.getTime())
						tEmployment.setLeave(true);
				} else {
					tEmployment.setLeave(true);
				}
				tEmployment.setComName(rs.getString("com_name"));
				tEmployment.setDescription(rs.getString("description"));
				tEmployment.setMoney(rs.getDouble("_money"));
				tEmployment.setPosition(rs.getString("position"));
				int userId = rs.getInt("user_id");
				TUser tUser = tUserDao.info(userId);
				tEmployment.settUser(tUser);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return tEmployment;
	}
}
